package DBLayer;

import ModelLayer.Customer;
import ModelLayer.Person;
import ModelLayer.Role;
import Prototypes.PersonResultSetModelBinder;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DBPersonContainer implements IPersonContainer
//public class DBPersonContainer extends DataLayer implements IPersonContainer
{
    private static DBPersonContainer instance;
    private Connection con;

    public DBPersonContainer()
    {
//        super();
        con = DbConnection.getInstance().getDBcon();
    }

    public int createPerson(Person pers)
    {
        int rs = -1;
        ResultSet rs2 = null;

        String query = "INSERT INTO WSPerson(name, adress, zipCode, country, phonenumber, email) VALUES('"
                + pers.getName() + "','"
                + pers.getAdress() + "','"
                + pers.getZipCode() + "','"
                + pers.getCountry() + "','"
                + pers.getPhonenumber() + "','"
                + pers.getEmail() + "')";

        String query2 = "SELECT MAX(personID) from WSPerson";
        int personId = -1;
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rs = stmt.executeUpdate(query);
            rs2 = stmt.executeQuery(query2);
            if (rs2.next())
            {
                personId = rs2.getInt(1);
            }
            stmt.close();

        }
        catch (Exception ex)
        {
            System.out.println("Insert exception in person db; " + ex);
        }
//        System.out.println(personId);
        return (personId);
    }

    public Person getPerson(int id)
    {
        try
        {
            String personSql = "SELECT * FROM WSPerson WHERE PersonID = " + id;
            Statement statement = con.createStatement();
            ResultSet results = statement.executeQuery(personSql);

            PersonResultSetModelBinder binder = new PersonResultSetModelBinder();
            Person person = binder.BindModel(results);

            DBCustomerContainer customerContainer = new DBCustomerContainer();
            Customer customer = customerContainer.getCustomer(id);

            ArrayList<Role> roles = new ArrayList<Role>();
            roles.add(customer);

            person.setRoles(roles);

            //Kode der indsætter de andre roller

            return person;
        }
        catch (SQLException ex)
        {
            Logger.getLogger(DBPersonContainer.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public Person getPerson(String where)
    {
        try
        {
            String personSql = "SELECT * FROM WSPerson WHERE " + where;
            Statement statement = con.createStatement();
            ResultSet results = statement.executeQuery(personSql);

            PersonResultSetModelBinder binder = new PersonResultSetModelBinder();
            Person person = binder.BindModel(results);

            DBCustomerContainer customerContainer = new DBCustomerContainer();
            Customer customer = customerContainer.getCustomer(person.getPersonID());

            ArrayList<Role> roles = new ArrayList<Role>();
            roles.add(customer);

            person.setRoles(roles);

            //Kode der indsætter de andre roller

            return person;
        }
        catch (SQLException ex)
        {
            Logger.getLogger(DBPersonContainer.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

//    public boolean createPerson2(Person pers)
//    {
//        int nrOfAffected = -1;
//        String query = "INSERT INTO Person(name, adress, zipCode, country, phonenumber, email) Values ('"+
//                pers.getName() +"','" +
//                pers.getAdress() +"','" +
//                pers.getZipCode() +"','" +
//                pers.getCountry() +"','" +
//                pers.getPhonenumber() +"','" +
//                pers.getEmail()+"')";
//        try
//        {
//            Statement insertState = dbConnection.createStatement();
//            insertState.setQueryTimeout(5);
//            nrOfAffected = insertState.executeUpdate(query);
//            insertState.close();
//        } catch (SQLException ex)
//        {
//            Logger.getLogger(DBPersonContainer.class.getName()).log(Level.SEVERE, null, ex);
//        }
//
//        return nrOfAffected > 0;
//    }
    //Static method that gives an instance of the class.
    public static DBPersonContainer getInstance()
    {
        if (instance == null)
        {
            instance = new DBPersonContainer();
        }
        return instance;
    }
}
